package com.intellekt.ecert.users.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;

import com.intellekt.ecert.domain.CatalogoVO;
import com.intellekt.ecert.domain.JdbcDao;
import com.intellekt.ecert.domain.JdbcQueryParameters;
import com.intellekt.ecert.domain.UsuarioVO;
import com.intellekt.ecert.users.vo.ItemVO;

public class UsersDaoImpl extends JdbcDao implements UsersWSDao {

	private String qryRoles;
	private String qryCountries;
	private String qryTransactions;

	private String qryUsers;
	private String updUsers;
	private String insUsers;
	private String delUsers;
	private String qryUserExist;

	private String qryPhytoItemsVU;
	private String qryPhytoItems;
	private String qryZooItemsVU;

	private JdbcQueryParameters qryItemsById;
	private String qryItemsByUser;

	private String insItem;
	private String delItem;
	private String qryItemExist;

	private static ParameterizedRowMapper<CatalogoVO> MAPPER_CATALOGO = new ParameterizedRowMapper<CatalogoVO>() {
		public CatalogoVO mapRow(ResultSet rs, int rowNum) throws SQLException {
			CatalogoVO obj = new CatalogoVO();
			obj.setKeyTxt(rs.getString("KEY"));
			obj.setValue(rs.getString("VALUE"));

			return obj;
		}
	};

	private static ParameterizedRowMapper<UsuarioVO> MAPPER_USR = new ParameterizedRowMapper<UsuarioVO>() {
		public UsuarioVO mapRow(ResultSet rs, int rowNum) throws SQLException {
			UsuarioVO obj = new UsuarioVO();

			obj.setUsername(rs.getString("USERNAME"));
			obj.setPassword(rs.getString("PASSWORD"));
			obj.setNombre(rs.getString("NOMBRE"));
			obj.setRolId(rs.getString("CVE_ROL"));
			obj.setRol(rs.getString("DES_ROL"));
			obj.setIdPais(rs.getString("CVE_PAIS"));
			obj.setPais(rs.getString("DES_PAIS"));
			if (rs.getString("CVE_ESTATUS").equals("A")) {
				obj.setVigente(true);
			} else {
				obj.setVigente(false);
			}

			return obj;
		}
	};

	private static ParameterizedRowMapper<ItemVO> MERC_MAPPER = new ParameterizedRowMapper<ItemVO>() {
		public ItemVO mapRow(ResultSet rs, int rowNum) throws SQLException {

			ItemVO obj = new ItemVO();
			obj.setId(rs.getString("ID_VIDA_SILVESTRE"));
			obj.setDesc(rs.getString("DESC_NOMBRE_COMUN"));
			obj.setScien(rs.getString("DESC_NOMBRE_CIENTIFICO"));

			return obj;
		}
	};

	@Override
	public List<UsuarioVO> getUsers() {
		if (log.isDebugEnabled()) {
			log.debug("getUsuarios...");
		}

		return (List<UsuarioVO>) jdbcTemplate.query(qryUsers, MAPPER_USR);
	}

	@Override
	public List<CatalogoVO> getRoles() {
		if (log.isDebugEnabled()) {
			log.debug("getRolesDao...");
		}
		return (List<CatalogoVO>) jdbcTemplate.query(qryRoles, MAPPER_CATALOGO);
	}

	@Override
	public List<CatalogoVO> getPaises() {
		if (log.isDebugEnabled()) {
			log.debug("getPaisesDao...");
		}
		return (List<CatalogoVO>) jdbcTemplate.query(qryCountries, MAPPER_CATALOGO);
	}

	@Override
	public List<CatalogoVO> getTransacciones() {
		if (log.isDebugEnabled()) {
			log.debug("getTransacciones...");
		}
		return (List<CatalogoVO>) jdbcTemplate.query(qryTransactions, MAPPER_CATALOGO);
	}

	@Override
	@SuppressWarnings({ "unchecked", "rawtypes" })
	public boolean userExist(String username) {
		if (log.isDebugEnabled()) {
			log.debug("existeUsername...");
		}
		ArrayList params = new ArrayList();
		params.add(username);

		try {
			jdbcTemplate.queryForObject(qryUserExist, params.toArray(), String.class);
			return true;
		} catch (EmptyResultDataAccessException e) {
			return false;
		}
	}

	/************************************************************************ FITO *********************************************************/

	@Override
	@SuppressWarnings({ "rawtypes", "unchecked" })
	public List<ItemVO> getItemsByUser(String username) {
		if (log.isDebugEnabled()) {
			log.debug("getMercanciasFito...");
		}
		ArrayList params = new ArrayList();
		params.add(username);
		return (List<ItemVO>) jdbcTemplate.query(qryItemsByUser, params.toArray(), MERC_MAPPER);
	}

	@Override
	public List<ItemVO> getItemsPhytoVU() {
		if (log.isDebugEnabled()) {
			log.debug("getMercanciasFitoVU...");
		}
		return (List<ItemVO>) jdbcTemplateVU.query(qryPhytoItemsVU, MERC_MAPPER);
	}

	@Override
	public List<ItemVO> getItemsPhyto(String idTransaccion, String username) {
		if (log.isDebugEnabled()) {
			log.debug("getItemsPhyto...");
		}
		List params = new ArrayList();
		params.add(idTransaccion);
		params.add(username);

		return (List<ItemVO>) jdbcTemplate.query(qryPhytoItems, params.toArray(), MERC_MAPPER);
	}

	/*
	 * @Override
	 * 
	 * @SuppressWarnings({ "rawtypes", "unchecked" }) public ItemVO
	 * getItemById(String idMercancia) { if (log.isDebugEnabled()) {
	 * log.debug("getItemsById..."); } ArrayList params = new ArrayList();
	 * params.add(idMercancia); return (ItemVO)
	 * jdbcTemplateVU.queryForObject(qryItemsById, params.toArray(),
	 * MERC_MAPPER); }
	 */

	@Override
	@SuppressWarnings("unchecked")
	public List<ItemVO> getItemsById(String idMercancia) {
		StringBuffer stb = new StringBuffer(qryItemsById.getSqlBase());

		Properties params = qryItemsById.getOptionalParameters();

		stb.append(" (");
		stb.append(idMercancia);
		stb.append(" )");

		if (log.isDebugEnabled()) {
			log.debug("getRegistros.... "+stb.toString());
		}

		return jdbcTemplateVU.query(stb.toString(), MERC_MAPPER);
	}

	/************************************************************************ ZOOO *********************************************************/

	@Override
	public List<ItemVO> getItemsZooVU() {
		if (log.isDebugEnabled()) {
			log.debug("getMercanciasZooVU...");
		}
		return (List<ItemVO>) jdbcTemplateVU.query(qryZooItemsVU, MERC_MAPPER);
	}

	@Override
	public void insItem(final ItemVO mercancia) {
		if (log.isDebugEnabled()) {
			log.debug("insertarMercanciaDao...");
		}

		jdbcTemplate.update(new PreparedStatementCreator() {
			public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
				PreparedStatement ps = con.prepareStatement(insItem);

				ps.setString(1, mercancia.getUsername());
				ps.setString(2, mercancia.getId());
				ps.setString(3, mercancia.getDesc());
				ps.setString(4, mercancia.getScien());

				return ps;
			}
		});

	}

	@Override
	public void delItem(final ItemVO mercancia) {
		if (log.isDebugEnabled()) {
			log.debug("borrarMercanciaDao...");
		}
		List<Object> parameters = new ArrayList<Object>();

		int affected = 0;
		parameters = new ArrayList<Object>();
		parameters.add("F");
		parameters.add(mercancia.getUsername());
		parameters.add(mercancia.getId());

		affected = jdbcTemplate.update(delItem, parameters.toArray());

		this.checkAffected(affected);
	}

	@Override
	@SuppressWarnings({ "rawtypes", "unchecked" })
	public boolean itemExist(final ItemVO mercancia) {
		ArrayList params = new ArrayList();
		params.add(mercancia.getUsername());
		params.add(mercancia.getId());

		if (jdbcTemplate.queryForObject(qryItemExist, params.toArray(), String.class).equals("1")) {
			return true;
		} else {
			return false;
		}
	}

	@Override
	public void insUser(final UsuarioVO vo) {
		if (log.isDebugEnabled()) {
			log.debug("insertaUsuarioDao...");
		}

		jdbcTemplate.update(new PreparedStatementCreator() {
			public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
				PreparedStatement ps = con.prepareStatement(insUsers);

				ps.setString(1, vo.getUsername());
				ps.setString(2, vo.getRolId());
				ps.setString(3, vo.getNombre());
				ps.setString(4, vo.getIdPais());
				ps.setString(5, vo.getPassword());
				ps.setString(6, vo.isVigente() ? "A" : "I");
				ps.setString(7, vo.getUsername());

				return ps;
			}
		});
	}

	@Override
	public void updUser(final UsuarioVO vo) {
		if (log.isDebugEnabled()) {
			log.debug("actualizaUsuarioDao...");
		}

		jdbcTemplate.update(new PreparedStatementCreator() {
			public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
				PreparedStatement ps = con.prepareStatement(updUsers);

				ps.setString(1, vo.getRolId());
				ps.setString(2, vo.getNombre());
				ps.setString(3, vo.getIdPais());
				ps.setString(4, vo.getPassword());
				ps.setString(5, vo.isVigente() ? "A" : "I");
				ps.setString(6, vo.getUsername());
				ps.setString(7, vo.getUsername());

				return ps;
			}
		});
	}

	@Override
	public void delUser(final UsuarioVO vo) {
		if (log.isDebugEnabled()) {
			log.debug("eliminaUsuarioDao...");
		}

		jdbcTemplate.update(new PreparedStatementCreator() {
			public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
				PreparedStatement ps = con.prepareStatement(delUsers);

				ps.setString(1, vo.getUsername());

				return ps;
			}
		});
	}

	public void setQryRoles(String qryRoles) {
		this.qryRoles = qryRoles;
	}

	public void setQryCountries(String qryCountries) {
		this.qryCountries = qryCountries;
	}

	public void setQryUsers(String qryUsers) {
		this.qryUsers = qryUsers;
	}

	public void setUpdUsers(String updUsers) {
		this.updUsers = updUsers;
	}

	public void setInsUsers(String insUsers) {
		this.insUsers = insUsers;
	}

	public void setDelUsers(String delUsers) {
		this.delUsers = delUsers;
	}

	public void setQryUserExist(String qryUserExist) {
		this.qryUserExist = qryUserExist;
	}

	public void setQryPhytoItemsVU(String qryPhytoItemsVU) {
		this.qryPhytoItemsVU = qryPhytoItemsVU;
	}

	public void setQryZooItemsVU(String qryZooItemsVU) {
		this.qryZooItemsVU = qryZooItemsVU;
	}

	public void setQryItemsByUser(String qryItemsByUser) {
		this.qryItemsByUser = qryItemsByUser;
	}

	public void setInsItem(String insItem) {
		this.insItem = insItem;
	}

	public void setDelItem(String delItem) {
		this.delItem = delItem;
	}

	public void setQryItemExist(String qryItemExist) {
		this.qryItemExist = qryItemExist;
	}

	public void setQryTransactions(String qryTransactions) {
		this.qryTransactions = qryTransactions;
	}

	public String getQryPhytoItems() {
		return qryPhytoItems;
	}

	public void setQryPhytoItems(String qryPhytoItems) {
		this.qryPhytoItems = qryPhytoItems;
	}

	public void setQryItemsById(JdbcQueryParameters qryItemsById) {
		this.qryItemsById = qryItemsById;
	}

	
}
